import pandas as pd

file1 = '2.xlsx'
file2 = '3.xlsx'

sheet_final = pd.read_excel(file1, 'Sheet1')
sheet_be_merged = pd.read_excel(file2, 'Sheet1')

# 合并的列是第2到6列（B列到F列）
start_col_index = 1
end_col_index = 5

# 列名范围
columns_to_merge = sheet_be_merged.columns[start_col_index:end_col_index + 1].tolist()
# 合并数据
merged_data = sheet_final.merge(sheet_be_merged[['Person'] + columns_to_merge], on='Person', how='left')

# 合并列名，检查是否有后缀
print("合并后的列名:", merged_data.columns.tolist())

for column in columns_to_merge:
    sheet_final[column] = merged_data[f'{column}_y']  # _y后缀

with pd.ExcelWriter('2.xlsx') as writer:
    sheet_final.to_excel(writer, sheet_name='Sheet1', index=False)

